﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class Sach_DAO
    {
        private static Sach_DAO _instance = null;

        public static Sach_DAO SACH
        {
            get
            {
                if (_instance == null)
                    _instance = new Sach_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// Them mot sach moi vao danh sach Sach
        /// </summary>
        /// <param name="sach"></param>
        public bool ThemSach(Sach_DTO sach)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemSach", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@TenSach", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@TheLoai", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@TacGia", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@SoLuongTon", SqlDbType.Int);

                // gan gia tri cho paramerers
                cmd.Parameters["@TenSach"].Value = sach.TenSach;
                cmd.Parameters["@TheLoai"].Value = sach.TheLoai;
                cmd.Parameters["@TacGia"].Value = sach.TacGia;
                cmd.Parameters["@SoLuongTon"].Value = sach.SoLuongTon;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="s"></param>
        public bool SuaSach(Sach_DTO s)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("SuaSach", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters.Add("@TenSach", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@TheLoai", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@TacGia", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@SoLuongTon", SqlDbType.Int);

                // gan gia tri cho paramerers
                cmd.Parameters["@MaSach"].Value = s.MaSach;
                cmd.Parameters["@TenSach"].Value = s.TenSach;
                cmd.Parameters["@TheLoai"].Value = s.TheLoai;
                cmd.Parameters["@TacGia"].Value = s.TacGia;
                cmd.Parameters["@SoLuongTon"].Value = s.SoLuongTon;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Xoa mot dau sach theo  MaSach
        /// </summary>
        /// <param name="masach"></param>
        public bool XoaSach(int masach)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaSach", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters["@MaSach"].Value = masach;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Lay thong tin mot dau sach theo MaSach
        /// </summary>
        /// <param name="ma"></param>
        /// <returns></returns>
        public DataTable LaySach(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("LaySach", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters["@MaSach"].Value = ma;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// Lay thong tin cua toan bo cac dau sach
        /// </summary>
        /// <param name="ma"></param>
        /// <returns></returns>
        public DataTable LayToanBoSach()
        {
            try
            {
                return SQLConnectData.DataConnection.GetDataFromStoredProcedure("LayToanBoSach");
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable TimSachTheoMa(int text)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TimSachTheoMa", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters["@MaSach"].Value = text;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table; ;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable TimSachTheoTen(string tensach)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TimSachTheoTen", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@TenSach", SqlDbType.NVarChar, 100);
                cmd.Parameters["@TenSach"].Value = tensach;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table; ;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable TimSachTheoTheLoai(string text)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TimSachTheoTheLoai", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@TheLoai", SqlDbType.NVarChar, 100);
                cmd.Parameters["@TheLoai"].Value = text;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table; ;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable TimSachTheoTacGia(string text)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TimSachTheoTacGia", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@TacGia", SqlDbType.NVarChar, 100);
                cmd.Parameters["@TacGia"].Value = text;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table; ;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable TimSachTheoTatCa(Sach_DTO sach)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TimSachTheoTatCa", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters.Add("@TenSach", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@TheLoai", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@TacGia", SqlDbType.NVarChar, 100);

                cmd.Parameters["@MaSach"].Value = sach.MaSach;
                cmd.Parameters["@TenSach"].Value = sach.TenSach;
                cmd.Parameters["@TheLoai"].Value = sach.TheLoai;
                cmd.Parameters["@TacGia"].Value = sach.TacGia;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table; ;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable TimTheoKhachHang(string ten)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TimTheoKhachHang", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 100);

                cmd.Parameters["@HoTen"].Value = ten;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataTable TimSachTheoSoLuongTon(int tu, int den)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TimSachTheoSoLuongTon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@Tu", SqlDbType.Int);
                cmd.Parameters.Add("@Den", SqlDbType.Int);
                
                cmd.Parameters["@Tu"].Value = tu;
                cmd.Parameters["@Den"].Value = den;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table;
            }
            catch (Exception)
            {
                return null;
            }
        }
    }

    public class ThamSo_DAO
    {
        private static ThamSo_DAO _thamSo = null;

        public static ThamSo_DAO THAMSO
        {
            get
            {
                if (_thamSo == null)
                    _thamSo = new ThamSo_DAO();

                return _thamSo;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="t"></param>
        public bool SuaThamSo(ThamSo_DTO t)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("SuaThamSo", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@SL_NhapToiThieu", SqlDbType.Int);
                cmd.Parameters.Add("@SL_TonToiThieuTruocKhiNhap", SqlDbType.Int);
                cmd.Parameters.Add("@SL_TonToiThieuSauKhiBan", SqlDbType.Int);
                cmd.Parameters.Add("@NoToiDa", SqlDbType.Money);
                cmd.Parameters.Add("@ApDung_QD_TienThuKhongVuotTienNo", SqlDbType.Bit);

                // gan gia tri cho paramerers
                cmd.Parameters["@SL_NhapToiThieu"].Value = t.SoLuongNhapToiThieu;
                cmd.Parameters["@SL_TonToiThieuTruocKhiNhap"].Value = t.SoLuongTonToiThieuTruocKhiNhap;
                cmd.Parameters["@SL_TonToiThieuSauKhiBan"].Value = t.SoLuongTonToiThieuSauKhiBan;
                cmd.Parameters["@NoToiDa"].Value = t.NoToiDa;
                cmd.Parameters["@ApDung_QD_TienThuKhongVuotTienNo"].Value = t.ApDungQuyDinh4;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int LaySLNhapToiThieu()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LaySLNhapToiThieu");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
            
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int LaySLTonToiThieuTruocKhiNhap()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LaySLTonToiThieuTruocKhiNhap");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int LaySLTonToiThieuSauKhiBan()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LaySLTonToiThieuSauKhiBan");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public float LaySLNoToiDa()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LaySLNoToiDa");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool ApDung_QD_TienThuKhongVuotTienNo()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("ApDung_QD_TienThuKhongVuotTienNo");
                DataRow row = table.Rows[0];
                return Convert.ToBoolean(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return false;
            }
        }
    }

    public class PhieuNhapSach_DAO
    {
        private static PhieuNhapSach_DAO _instance = null;

        public static PhieuNhapSach_DAO PHIEUNHAPSACH
        {
            get
            {
                if (_instance == null)
                    _instance = new PhieuNhapSach_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p"></param>
        public bool ThemPhieuNhapSach(PhieuNhapSach_DTO p)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemPhieuNhapSach", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@NgayNhap", SqlDbType.SmallDateTime);

                // gan gia tri cho paramerers
                cmd.Parameters["@NgayNhap"].Value = p.NgayNhap;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaPhieuNhapSach(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaPhieuNhapSach", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaPhieuNhap", SqlDbType.Int);
                cmd.Parameters["@MaPhieuNhap"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Lay MaPhieuNhap lon nhat
        /// </summary>
        /// <returns></returns>
        public int LayMaPhieuNhapLonNhat()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LayMaPhieuNhapLonNhat");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
        }
        
    }

    public class ChiTietPhieuNhap_DAO
    {
        private static ChiTietPhieuNhap_DAO _instance = null;

        public static ChiTietPhieuNhap_DAO CHITIETPHIEUNHAP
        {
            get
            {
                if (_instance == null)
                    _instance = new ChiTietPhieuNhap_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="c"></param>
        public bool ThemChiTietPhieuNhap(ChiTietPhieuNhap_DTO c)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemChiTietPhieuNhap", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaPhieuNhap", SqlDbType.Int);
                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters.Add("@SoLuongNhap", SqlDbType.Int);
                cmd.Parameters.Add("@DonGiaNhap", SqlDbType.Money);
                // gan gia tri cho paramerers
                cmd.Parameters["@MaPhieuNhap"].Value = c.MaPhieuNhap;
                cmd.Parameters["@MaSach"].Value = c.MaSach;
                cmd.Parameters["@SoLuongNhap"].Value = c.SoLuongNhap;
                cmd.Parameters["@DonGiaNhap"].Value = c.DonGiaNhap;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaChiTietPhieuNhap(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaChiTietPhieuNhap", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaChiTietPhieuNhap", SqlDbType.Int);

                cmd.Parameters["@MaChiTietPhieuNhap"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }

    public class HoaDon_DAO
    {
        private static HoaDon_DAO _instance = null;

        public static HoaDon_DAO HOADON
        {
            get
            {
                if (_instance == null)
                    _instance = new HoaDon_DAO();

                return _instance;
            }
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="h"></param>
        public bool ThemHoaDon(HoaDon_DTO h)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemHoaDon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@NgayHoaDon", SqlDbType.SmallDateTime);
                cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                cmd.Parameters.Add("@TongTien", SqlDbType.Money);

                // gan gia tri cho paramerers
                cmd.Parameters["@NgayHoaDon"].Value = h.NgayHoaDon;
                cmd.Parameters["@MaKhachHang"].Value = h.MaKhachHang;
                cmd.Parameters["@TongTien"].Value = h.TongTien;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaHoaDon(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaHoaDon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaHoaDon", SqlDbType.Int);

                cmd.Parameters["@MaHoaDon"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public int LayMaHoDonLonNhat()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LayMaHoDonLonNhat");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
        }
    }

    public class ChiTietHoaDon_DAO
    {
        private static ChiTietHoaDon_DAO _instance = null;

        public static ChiTietHoaDon_DAO CHITIETHOADON
        {
            get
            {
                if (_instance == null)
                    _instance = new ChiTietHoaDon_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="c"></param>
        public bool ThemChiTietHoaDon(ChiTietHoaDon_DTO c)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemChiTietHoaDon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaHoaDon", SqlDbType.Int);
                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters.Add("@SoLuongBan", SqlDbType.Int);
                cmd.Parameters.Add("@DonGiaBan", SqlDbType.Money);

                // gan gia tri cho paramerers
                cmd.Parameters["@MaHoaDon"].Value = c.MaHoaDon;
                cmd.Parameters["@MaSach"].Value = c.MaSach;
                cmd.Parameters["@SoLuongBan"].Value = c.SoLuongBan;
                cmd.Parameters["@DonGiaBan"].Value = c.DonGiaBan;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaChiTietHoaDon(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaChiTietHoaDon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaChiTietHoaDon", SqlDbType.Int);

                cmd.Parameters["@MaChiTietHoaDon"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }

    public class KhachHang_DAO
    {
        private static KhachHang_DAO _instance = null;

        public static KhachHang_DAO KHACHHANG
        {
            get
            {
                if (_instance == null)
                    _instance = new KhachHang_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="kh"></param>
        public bool ThemKhachHang(KhachHang_DTO kh)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemKhachHang", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 200);
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@DienThoai", SqlDbType.NVarChar, 20);
                cmd.Parameters.Add("@TienNo", SqlDbType.Money);

                // gan gia tri cho paramerers
                cmd.Parameters["@HoTen"].Value = kh.HoTen;
                cmd.Parameters["@DiaChi"].Value = kh.DiaChi;
                cmd.Parameters["@Email"].Value = kh.Email;
                cmd.Parameters["@DienThoai"].Value = kh.DienThoai;
                cmd.Parameters["@TienNo"].Value = kh.TienNo;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="kh"></param>
        public bool SuaKhachHang(KhachHang_DTO kh)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("SuaKhachHang", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaKhachHang", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar, 200);
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@DienThoai", SqlDbType.NVarChar, 20);
                cmd.Parameters.Add("@TienNo", SqlDbType.Money);

                // gan gia tri cho paramerers
                cmd.Parameters["@MaKhachHang"].Value = kh.MaKhachHang;
                cmd.Parameters["@HoTen"].Value = kh.HoTen;
                cmd.Parameters["@DiaChi"].Value = kh.DiaChi;
                cmd.Parameters["@Email"].Value = kh.Email;
                cmd.Parameters["@DienThoai"].Value = kh.DienThoai;
                cmd.Parameters["@TienNo"].Value = kh.TienNo;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaKhachHang(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaKhachHang", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                cmd.Parameters["@MaKhachHang"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        /// <returns></returns>
        public DataTable LayKhachHang(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("LayKhachHang", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                cmd.Parameters["@MaKhachHang"].Value = ma;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table; ;
            }
            catch (Exception)
            {
                return null;
            }
        }
    }

    public class PhieuThuTien_DAO
    {
        private static PhieuThuTien_DAO _instance = null;

        public static PhieuThuTien_DAO PHIEUTHUTIEN
        {
            get
            {
                if (_instance == null)
                    _instance = new PhieuThuTien_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p"></param>
        public bool ThemPhieuThuTien(PhieuThuTien_DTO p)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemPhieuThuTien", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                cmd.Parameters.Add("@NgayThuTien", SqlDbType.SmallDateTime);
                cmd.Parameters.Add("@SoTienThu", SqlDbType.Money);

                // gan gia tri cho paramerers
                cmd.Parameters["@MaKhachHang"].Value = p.MaKhachHang;
                cmd.Parameters["@NgayThuTien"].Value = p.NgayThuTien;
                cmd.Parameters["@SoTienThu"].Value = p.SoTienThu;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaPhieuThuTien(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaPhieuThuTien", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaPhieuThu", SqlDbType.Int);

                cmd.Parameters["@MaPhieuThu"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int LayMaPhieuThuLonNhat()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LayMaPhieuThuLonNhat");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
        }
    }

    public class BaoCaoTon_DAO
    {
        private static BaoCaoTon_DAO _instance = null;

        public static BaoCaoTon_DAO BAOCAOTON
        {
            get
            {
                if (_instance == null)
                    _instance = new BaoCaoTon_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p"></param>
        public bool ThemBaoCaoTon(BaoCaoTon_DTO p)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemBaoCaoTon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@Thang", SqlDbType.SmallDateTime);

                // gan gia tri cho paramerers
                cmd.Parameters["@Thang"].Value = p.Thang;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaBaoCaoTon(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaBaoCaoTon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaBaoCao", SqlDbType.Int);

                cmd.Parameters["@MaBaoCao"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public int LayMaBaoCaoTonLonNhat()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LayMaBaoCaoTonLonNhat");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
        }

        public DataTable TaoBaoCaoTon(string day)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TaoBaoCaoTon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@NgayLap", SqlDbType.SmallDateTime);

                cmd.Parameters["@NgayLap"].Value = day;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table;
            }
            catch (Exception)
            {
                return null;
            }
        }
    }

    public class ChiTietBaoCaoTon_DAO
    {
        private static ChiTietBaoCaoTon_DAO _instance = null;

        public static ChiTietBaoCaoTon_DAO CHITIETBAOCAOTON
        {
            get
            {
                if (_instance == null)
                    _instance = new ChiTietBaoCaoTon_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p"></param>
        public bool ThemChiTietBaoCaoTon(ChiTietBaoCaoTon_DTO p)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemChiTietBaoCaoTon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaBaoCao", SqlDbType.Int);
                cmd.Parameters.Add("@MaSach", SqlDbType.Int);
                cmd.Parameters.Add("@TonDau", SqlDbType.Int);
                cmd.Parameters.Add("@PhatSinh", SqlDbType.Int);
                cmd.Parameters.Add("@TonCuoi", SqlDbType.Int);

                // gan gia tri cho paramerers
                cmd.Parameters["@MaBaoCao"].Value = p.MaBaoCao;
                cmd.Parameters["@MaSach"].Value = p.MaSach;
                cmd.Parameters["@TonDau"].Value = p.TonDau;
                cmd.Parameters["@PhatSinh"].Value = p.PhatSinh;
                cmd.Parameters["@TonCuoi"].Value = p.TonCuoi;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaChiTietBaoCaoTon(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaChiTietBaoCaoTon", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaChiTietBaoCaoTon", SqlDbType.Int);

                cmd.Parameters["@MaChiTietBaoCaoTon"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }

    public class BaoCaoCongNo_DAO
    {
        private static BaoCaoCongNo_DAO _instance = null;

        public static BaoCaoCongNo_DAO BAOCAOCONGNO
        {
            get
            {
                if (_instance == null)
                    _instance = new BaoCaoCongNo_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p"></param>
        public bool ThemBaoCaoCongNo(BaoCaoCongNo_DTO p)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemBaoCaoCongNo", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@Thang", SqlDbType.SmallDateTime);

                // gan gia tri cho paramerers
                cmd.Parameters["@Thang"].Value = p.Thang;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaBaoCaoCongNo(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaBaoCaoCongNo", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaBaoCao", SqlDbType.Int);

                cmd.Parameters["@MaBaoCao"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public int LayMaBaoCaoCongNoLonNhat()
        {
            try
            {
                DataTable table = SQLConnectData.DataConnection.GetDataFromStoredProcedure("LayMaBaoCaoCongNoLonNhat");
                DataRow row = table.Rows[0];
                return Convert.ToInt32(row.ItemArray[0]);
            }
            catch (Exception)
            {
                return -999;
            }
        }

        public DataTable TaoBaoCaoCongNo(string day)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("TaoBaoCaoCongNo", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@NgayLap", SqlDbType.SmallDateTime);

                cmd.Parameters["@NgayLap"].Value = day;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table;
            }
            catch (Exception)
            {
                return null;
            }
        }
    }

    public class ChiTietBaoCaoCongNo_DAO
    {
        private static ChiTietBaoCaoCongNo_DAO _instance = null;

        public static ChiTietBaoCaoCongNo_DAO CHITIETBAOCAOCONGNO
        {
            get
            {
                if (_instance == null)
                    _instance = new ChiTietBaoCaoCongNo_DAO();

                return _instance;
            }
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="p"></param>
        public bool ThemChiTietBaoCaoCongNo(ChiTietBaoCaoCongNo_DTO p)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemChiTietBaoCaoCongNo", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaBaoCao", SqlDbType.Int);
                cmd.Parameters.Add("@MaKhachHang", SqlDbType.Int);
                cmd.Parameters.Add("@NoDau", SqlDbType.Money);
                cmd.Parameters.Add("@PhatSinh", SqlDbType.Money);
                cmd.Parameters.Add("@NoCuoi", SqlDbType.Money);

                // gan gia tri cho paramerers
                cmd.Parameters["@MaBaoCao"].Value = p.MaBaoCao;
                cmd.Parameters["@MaKhachHang"].Value = p.MaKhachHang;
                cmd.Parameters["@NoDau"].Value = p.NoDau;
                cmd.Parameters["@PhatSinh"].Value = p.PhatSinh;
                cmd.Parameters["@NoCuoi"].Value = p.NoCuoi;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaChiTietBaoCaoCongNo(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaChiTietBaoCaoCongNo", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaChiTietBaoCaoCongNo", SqlDbType.Int);

                cmd.Parameters["@MaChiTietBaoCaoCongNo"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }

    public class NhanVien_DAO
    {
        private static NhanVien_DAO _instance = null;

        public static NhanVien_DAO NHANVIEN
        {
            get
            {
                if (_instance == null)
                    _instance = new NhanVien_DAO();

                return _instance;
            }
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="kh"></param>
        public bool ThemNhanVien(NhanVien_DTO kh)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemNhanVien", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 70);
                cmd.Parameters.Add("@MatKhau", SqlDbType.Char, 30);
                cmd.Parameters.Add("@MaViTri", SqlDbType.Int);

                // gan gia tri cho paramerers
                cmd.Parameters["@HoTen"].Value = kh.HoTen;
                cmd.Parameters["@DiaChi"].Value = kh.MatKhau;
                cmd.Parameters["@Email"].Value = kh.MaViTri;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="kh"></param>
        public bool SuaNhanVien(NhanVien_DTO kh)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("SuaNhanVien", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaNhanVien", SqlDbType.Int);
                cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar, 70);
                cmd.Parameters.Add("@MatKhau", SqlDbType.Char, 30);
                cmd.Parameters.Add("@MaViTri", SqlDbType.Int);

                // gan gia tri cho paramerers
                cmd.Parameters["@MaNhanVien"].Value = kh.MaNhanVien;
                cmd.Parameters["@HoTen"].Value = kh.HoTen;
                cmd.Parameters["@MatKhau"].Value = kh.MatKhau;
                cmd.Parameters["@MaViTri"].Value = kh.MaViTri;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaNhanVien(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaNhanVien", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaNhanVien", SqlDbType.Int);
                cmd.Parameters["@MaNhanVien"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Lay thong tin cua mot nhan vien theo MaNhanVien
        /// </summary>
        /// <param name="ma"></param>
        /// <returns></returns>
        public DataTable LayNhanVien(int ma)
        {
            try
            {
            SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
            SqlCommand cmd = new SqlCommand("LayNhanVien", cnn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@MaNhanVien", SqlDbType.Int);
            cmd.Parameters["@MaNhanVien"].Value = ma;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            da.Fill(table);

            return table;
            }
            catch (Exception)
            {
                return null;
            }
        }
    }

    public class ViTri_DAO
    {
        private static ViTri_DAO _instance = null;

        public static ViTri_DAO VITRI
        {
            get
            {
                if (_instance == null)
                    _instance = new ViTri_DAO();

                return _instance;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="v"></param>
        public bool ThemViTri(ViTri_DTO v)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("ThemViTri", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@NhapSach", SqlDbType.Bit);
                cmd.Parameters.Add("@LapHoaDon", SqlDbType.Bit);
                cmd.Parameters.Add("@TraCuuSach", SqlDbType.Bit);
                cmd.Parameters.Add("@LapPhieuThuTien", SqlDbType.Bit);
                cmd.Parameters.Add("@LapBaoCao", SqlDbType.Bit);
                cmd.Parameters.Add("@LapBaoCao", SqlDbType.Bit);

                // gan gia tri cho paramerers
                cmd.Parameters["@NhapSach"].Value = v.NhapSach;
                cmd.Parameters["@LapHoaDon"].Value = v.LapHoaDon;
                cmd.Parameters["@TraCuuSach"].Value = v.TraCuuSach;
                cmd.Parameters["@LapPhieuThuTien"].Value = v.LapPhieuThuTien;
                cmd.Parameters["@LapBaoCao"].Value = v.LapBaoCao;
                cmd.Parameters["@ThayDoiQuyDinh"].Value = v.ThayDoiQuyDinh;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="v"></param>
        public bool SuaViTri(ViTri_DTO v)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("SuaViTri", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                // khai bao parameters
                cmd.Parameters.Add("@MaViTri", SqlDbType.Int);
                cmd.Parameters.Add("@NhapSach", SqlDbType.Bit);
                cmd.Parameters.Add("@LapHoaDon", SqlDbType.Bit);
                cmd.Parameters.Add("@TraCuuSach", SqlDbType.Bit);
                cmd.Parameters.Add("@LapPhieuThuTien", SqlDbType.Bit);
                cmd.Parameters.Add("@LapBaoCao", SqlDbType.Bit);
                cmd.Parameters.Add("@LapBaoCao", SqlDbType.Bit);

                // gan gia tri cho paramerers
                cmd.Parameters["@MaViTri"].Value = v.MaViTri;
                cmd.Parameters["@NhapSach"].Value = v.NhapSach;
                cmd.Parameters["@LapHoaDon"].Value = v.LapHoaDon;
                cmd.Parameters["@TraCuuSach"].Value = v.TraCuuSach;
                cmd.Parameters["@LapPhieuThuTien"].Value = v.LapPhieuThuTien;
                cmd.Parameters["@LapBaoCao"].Value = v.LapBaoCao;
                cmd.Parameters["@ThayDoiQuyDinh"].Value = v.ThayDoiQuyDinh;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ma"></param>
        public bool XoaViTri(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("XoaViTri", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaViTri", SqlDbType.Int);
                cmd.Parameters["@MaViTri"].Value = ma;

                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Lay quyen han cua mot nha vien theo MaViTri
        /// </summary>
        /// <param name="ma"></param>
        /// <returns></returns>
        public DataTable LayQuyenHan(int ma)
        {
            try
            {
                SqlConnection cnn = SQLConnectData.DataConnection.SqlConnection;
                SqlCommand cmd = new SqlCommand("LayQuyenHan", cnn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@MaViTri", SqlDbType.Int);
                cmd.Parameters["@MaViTri"].Value = ma;

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable table = new DataTable();
                da.Fill(table);

                return table;
            }
            catch (Exception)
            {
                return null;
            }
        }
    }
}
